﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Modellayer;

namespace Dblayer
{
    public class DbMovie
    {
        SqlCommand cmd = null;

        public DbMovie()
        { 
        }

        public void createMovie(Movie m)
        {
            try
            {
                Connection.dbconn.Open();

                string insert = @"insert into Movie(Title, MovieLength, MovieDescription)
                values ('" + m.Title +"'," + "'" + m.MovieLength + "'," + "'" + m.MovieDescription + "')";

                SqlCommand mycomand = new SqlCommand(insert, Connection.dbconn);
                mycomand.ExecuteReader();
            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (Connection.dbconn != null)
                {
                    Connection.dbconn.Close();
                }

            }
        }

            public Movie findMovie(string title)
            {
                string titlee = @"SELECT * FROM Movie WHERE Title = '" + title + "'";
                return buildMovie(titlee);
            }

            private Movie buildMovie(string title)
            {
                System.Data.IDataReader dbReader;
                Movie m = null;

                try
                {
                    cmd = Connection.GetDBCommand(title);
                    dbReader = cmd.ExecuteReader();
                    while(dbReader.Read())
                    {
                        m = new Movie();
                        m.Title = dbReader["Title"].ToString();
                        m.MovieLength = dbReader["MovieLength"].ToString();
                        m.MovieDescription = dbReader["MovieDescription"].ToString();

  
                    }
                    Connection.dbconn.Close();
                }
                catch(Exception e)
                {
                    Console.WriteLine("{0}", e.Message);
                    m = null;
                }
                return m;
            }

            public List<Movie> findAllMovies()
            {
                string sql = @"SELECT * FROM Movie";
                return buildAllMovie(sql);
            }

            private List<Movie> buildAllMovie(string sql)
            {
                List<Movie> movie = new List<Movie>();
                IDataReader dbReader;
                Movie m = null;

                try 
                {
                    cmd = Connection.GetDBCommand(sql);
                    dbReader = cmd.ExecuteReader();
                    while(dbReader.Read())
                    {
                        m = new Movie();
                        m.Title = dbReader["Title"].ToString();
                        m.MovieLength = dbReader["MovieLength"].ToString();
                        m.MovieDescription = dbReader["MovieDescription"].ToString();
                        movie.Add(m);
                    }
                    Connection.dbconn.Close();
                    dbReader.Close();
                }
                catch(Exception e)
                {
                    Console.WriteLine("{0}", e.Message);
                }
      
             
                return movie;

                }

            public void updateMovie(Movie m)
            {
                try
                {
                    Connection.dbconn.Open();

                    string update = @"UPDATE Movie SET " + " MovieLength ='" + m.MovieLength + "'," + "MovieDescription ='" + m.MovieDescription
                        + "'," + "WHERE Title ='" + m.Title + "'";
                    cmd = Connection.GetDBCommand(update);
                    cmd.ExecuteReader();
                }
                catch (Exception e)
                {
                    Console.WriteLine("{0}", e.Message);
                }
                finally
                {
                    if (Connection.dbconn != null)
                    {
                        Connection.dbconn.Close();
                    }
                }
            }

        }
    }

